import pymysql


class DB():
    def __init__(self, host, user, password, database, cursorclass=None):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.cursorclass = cursorclass

    def connect(self):
        self.db = pymysql.connect(host=self.host, user=self.user, password=self.password, database=self.database,
                                  cursorclass=self.cursorclass)
        self.cursor = self.db.cursor()

    def close(self):
        self.db.close()
        self.cursor.close()

    def select_one(self, sql):
        self.connect()
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchone()
            self.close()
        except Exception as e:
            print('sql语句错误', e)
            result = None
        return result

    def select_all(self, sql):
        self.connect()
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            self.close()
        except Exception as e:
            print('sql语句错误', e)
            result = None
        return result

    def __edit(self, sql):
        self.connect()
        try:
            self.cursor.execute(sql)
            self.db.commit()
            result = '数据库更新成功'
        except Exception as e:
            print('数据库更新失败', e)
            result = None
        return result

    def insert(self, sql):
        return self.__edit(sql)

    def update(self, sql):
        return self.__edit(sql)

    def delete(self, sql):
        return self.__edit(sql)

    def create_table(self, sql):
        self.connect()
        try:
            self.cursor.execute(sql)
            self.db.commit()
            result = '数据表创建成功'
        except Exception as e:
            print('数据表创建失败', e)
            result = None
        return result


class Sunji(DB):
    def suijichouqu(self, sql):
        self.connect()
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchone()
            self.close()
        except Exception as e:
            print('查询失败')
            result = None
        return f"{result['name']}到"


sql = DB('121.43.51.170', 'root', '19980705', 'fdy', pymysql.cursors.DictCursor)
xx_obj = Sunji('121.43.51.170', 'root', '19980705', 'fdy', pymysql.cursors.DictCursor)


# print(sql.select_one('select * from student'))
# print(sql.create_table(
#     'create table user_student(id int PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50),age INT,address VARCHAR(100),phone VARCHAR(20));'))
# print(sql.insert("insert into user_student(name, age, address, phone) values('小天才',25,'沈阳','13840103191')"))
# print(sql.insert("insert into user_student(name, age, address, phone) values('冯定宇',25,'北京','13213451234')"))
# print(sql.update("update user_student set name = '秦彪' where id = 4"))

# print(sql.delete("delete from user_student where name = '秦彪'"))
# print(sql.select_all('select * from user_student'))
# print(xx_obj.suijichouqu("select * from user_student order by rand() limit 1"))

class Class_manage(DB):
    def create1(self, sql):
        self.create_table(sql)

    def select_text(self, sql):
        self.connect()
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchall()

            self.close()
        except Exception as e:
            print('查询失败')
            result = None
        return result


c1 = Class_manage('121.43.51.170', 'root', '19980705', 'fdy', pymysql.cursors.DictCursor)

# print(c1.create_table(
# 'create table student_manage(id int primary key auto_increment,name varchar(50),english int,math int,chinese int,class varchar(20))'))

import random
from faker import Faker

faker = Faker(locale='zh_CN')

# for i in range(100):
#     class_stu = random.choice(['1809A', '1809B', '1810A', '1810B'])
#     english_grade = random.randint(50, 100)
#     math_grade = random.randint(50, 100)
#     chinese_grade = random.randint(50, 100)
#     name = faker.name()
#     print(c1.insert(
#         f"insert into student_manage(name,english,math,chinese,class) values ('{name}',{english_grade},{math_grade},{chinese_grade},'{class_stu}')"))


# result = c1.select_all('select name,math,class from student_manage order by math desc')
# list_1809A = []
# list_1809B = []
# list_1810A = []
# list_1810B = []
# for i in result:
#     if i['class'] == '1809A':
#         list_1809A.append(i)
#     elif i['class'] == '1809B':
#         list_1809B.append(i)
#     elif i['class'] == '1810A':
#         list_1810A.append(i)
#     elif i['class'] == '1810B':
#         list_1810B.append(i)
# print(list_1809A)
# print(list_1809B)
# print(list_1810A)
# print(list_1810B)

result = c1.select_all('select class 班级,count(*) 人数 from student_manage group by class order by 人数 desc')
print(result)

result = c1.select_all('select * from student_manage where english < 60 or math < 60 or chinese < 60')

class_dict = {}

for i in result:
    class_name = i['class']
    if class_name not in class_dict:
        class_dict[class_name] = []
    class_dict[class_name].append(i)
print(class_dict)

for class_name, students in class_dict.items():
    print(f'班级：{class_name}')
    for student in students:
        print(f"姓名：{student['name']}, 数学成绩：{student['math']}, 英语成绩：{student['english']}, 语文成绩：{student['chinese']}")


"""
4、定义一个图书管理系统类，图书可以新增、修改、删除，要求新增、修改、删除的图书，都要出现在数据库中
新增：图书名、作者名称、新建年月日
修改：图书名、作者名称、新建年月日
删除：图书删掉，且不在展示
查看：全量查询、精确查询、模糊查询需要用户传入形参来控制返回结果
"""


class books(DB):
    def create_book(self, sql):
        self.connect()
        try:
            self.cursor.execute(sql)
            self.db.commit()
            result = '图书表创建成功'
        except:
            print('创建失败')
            result = None
        return result

b1 = books('121.43.51.170', 'root', '19980705', 'fdy', pymysql.cursors.DictCursor)
print(b1.create_book(
    'create table book_list(id int PRIMARY KEY AUTO_INCREMENT,book_name VARCHAR(50),zuozhe VARCHAR(50),book_date date)'))
